<?php
/**
 * Created by PhpStorm.
 * User: dingjinlai
 * Date: 2019/5/5
 * Time: 下午5:44
 */

class Ga_goods_model extends CI_Model
{
    public $limit_rows = 15;

    private $_cate = array();
    private $cate = array();

    public $table_name = 'ga_goods';

    public function cate($pid = 0)
    {
        $this->db->from('category')->where('is_delete', 0);

        if ($pid) {
            $this->db->where("pid = '{$pid}'");
        }

        $query = $this->db->get();
        $_cate = $query->result_array();


        if ($pid) {
            $cate_id = array();
            foreach ($_cate as $value) {
                $cate_id[] = $value['cate_id'];
            }
            return $cate_id;
        } else {
            $cate = array();
            foreach ($_cate as $value) {
                $cate[$value['cate_id']] = $value;
            }
            $cate_tree = $this->tree_cate($cate);

            return $cate_tree;
        }
    }

    public function tree_cate($cate)
    {
        $tree = array();
        foreach ($cate as $item) {
            if ($cate[$item['pid']]) {
                $cate[$item['pid']]['item'][$item['cate_id']] = &$cate[$item['cate_id']];
            } else {
                $tree[$item['cate_id']] = &$cate[$item['cate_id']];
            }
        }

        return $tree;
    }

    /**
     * 商品列表
     * @param array $param
     * @param int   $is_all
     *
     * @return array
     * goods_list
     * author: walker
     * Date: 2019/12/4
     * Time: 18:24
     * Note:
     */
    public function goods_list($param = array(), $is_all = 0)
    {

        if ($param['cate_id']) {

            $end_cate_id = end($param['cate_id']);

            $cate = $this->cate($end_cate_id);

            $param['cate_id'] = array_merge(array($end_cate_id), $cate);
        }

        $param['sale_count'] = $param['sale_count'] ? $param['sale_count'] : 3;
        $param['web_id'] = array_filter($param['web_id']);

        if ($param['web_id']) {
            $web_ids_str = " and oi.web_id in ( " . implode(',', $param['web_id']) . ")";
        }

        if ($param['statistical_time'][0] && $param['statistical_time'][1]) {
            $_day_start =  strtotime($param['statistical_time'][0] . " 12:00:00");
            $_day_end = strtotime($param['statistical_time'][1] . " 12:00:00");
            $statistical_where = " and oi.add_time >= '{$_day_start}' and oi.add_time <= '{$_day_end}' ";
        }

        $order_sql = "select SUM(og.goods_number * og.goods_price) from pcs_order_goods as og left join pcs_order_info as oi on og.order_id = oi.order_id where og.goods_id = g.goods_id {$web_ids_str} {$statistical_where}";

        $this->db->from('goods as g')
            ->select("g.goods_id,({$order_sql}) as goods_num_price,g.goods_sn,c.cate_name,g.is_on_sale,g.original_img,g.cat_id,g.admin_id,g.is_on_sale,g.suppliers_id,g.in_price,g.supp_url,g.suppliers_id,s.suppliers_name")
            ->join('suppliers as s', 'g.suppliers_id = s.suppliers_id', 'left')
            ->join('category as c', 'c.cate_id = g.cat_id', 'left');

        if($param['goods_ids']){
            if(!is_array($param['goods_ids'])){
                $param['goods_ids'] = [$param['goods_ids']];
            }
            $this->db->where_in('goods_id', $param['goods_ids']);
        }

        if ($statistical_where) {
            $sql_sale_count = $statistical_where;
        } else {
            if ($param['sale_count']) {
                $sale_count = strtotime("-{$param['sale_count']} day");
                $sql_sale_count = " and oi.add_time >= '{$sale_count}' ";
            }
        }



        $order_sale_sql = "(select sum(ogg.goods_number) from pcs_order_goods as ogg left join pcs_order_info as oi on ogg.order_id = oi.order_id where ogg.goods_id = g.goods_id {$sql_sale_count} {$web_ids_str}) as sale_count";

        $this->db->select("{$order_sale_sql}");
        $this->db->order_by('sale_count DESC, goods_num_price DESC');

        $this->_where($param);

        if ($is_all == 0) {
            $this->db->limit($this->limit_rows,$this->limit_rows * $param['page']);
        }

        $query = $this->db->get();

        $_list = $query->result_array();
        //print_r($this->db->last_query());

        return $_list;
    }

    public function goods_sku_list($param = array())
    {

        $web = $this->siteweb_option();

        $this->db->from('order_goods as og')
            ->select('SUM(og.goods_number) as goods_number,SUM(og.goods_number * og.goods_price) as goods_num_price,gs.sku_num_sn,gs.sku_id, gs.color,gs.size,oi.web_id,gs.goods_id')
            ->join('goods_sku as gs', 'og.sku_id = gs.sku_id', 'left')
            ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
            ->where("gs.goods_id = '{$param['goods_id']}'")
            ->where_in('oi.web_id', $param['web_ids'])
            ->group_by('gs.sku_id,oi.web_id');


        $query = $this->db->get();

        $_list = $query->result_array();

        $list = array();
        foreach ($_list as $item) {
            $web_name = $web[$item['web_id']];
            $color = str_replace(' ', '-', $item['color']);

            //$list['to_goods_num_price'] += $item['goods_num_price'];
            $list[$color]['goods_number'] += $item['goods_number'];
            $list[$color]['goods_num_price'] += $item['goods_num_price'];
            $list[$color]['item'][$web_name]['goods_number'] += $item['goods_number'];
            $list[$color]['size'][$item['sku_id']]['title'] = $item['size'];
            $list[$color]['size'][$item['sku_id']]['goods_number'] += $item['goods_number'];
            $list[$color]['size'][$item['sku_id']]['goods_num_price'] += $item['goods_num_price'];
            $list[$color]['size'][$item['sku_id']]['item'][$web_name]['goods_number'] += $item['goods_number'];
        }

        $ga_list = $this->ga_goods_sku_list(array(
            'goods_id' => $param['goods_id']
        ));

        foreach ($ga_list as $k => $row) {
            $list[$k]['date'] = $row['date'];
            foreach ($row['item'] as $size_k => $size_row) {
                $list[$k]['size'][$size_k]['date'] = $size_row;
            }
        }

        return $list;
    }

    public function goods_sku_ids($goods_ids)
    {
        $query = $this->db->from('goods_sku')
            ->select('color,size, goods_id')
            ->where_in('goods_id', $goods_ids)
            ->group_by('color,size,goods_id')
            ->get();

        $_sku = $query->result_array();

        $sku = array();
        foreach ($_sku as $item) {
            $sku[$item['goods_id']][$item['color']]['title'] = $item['color'];
            $sku[$item['goods_id']][$item['color']]['size'] += 1;
        }

        return $sku;
    }

    public function goods_sale($param)
    {

        $order_num = "select sum(og.goods_number) from pcs_order_goods as og left join pcs_order_info as oi on og.order_id = oi.order_id where sg.web_id = oi.web_id and sg.goods_id = og.goods_id";

        $this->db->from('shopify_goods as sg')
            ->select("({$order_num}) as order_num, sg.web_id, sg.goods_id, sg.add_time,s.web_name")
            ->join('siteweb as s', 'sg.web_id = s.web_id', 'left')
            ->order_by('field(sg.goods_id, ' . implode(',', $param['goods_ids']) . '), order_num DESC');

        if ($param['goods_ids']) {
            $this->db->where_in('goods_id', $param['goods_ids']);
        }

        $query = $this->db->get();

        $_list = $query->result_array();

        $_goods_list = array();
        $today = strtotime('today');
        foreach ($_list as $item) {
            $item['day'] = ($today - strtotime(date('Y-m-d', $item['add_time']))) / (24 * 3600);
            $_goods_list[' '.$item['goods_id']][$item['web_id']] = $item;
        }

        return $_goods_list;

    }

    public function goods_sku($goods_ids)
    {
        $query = $this->db->from('goods_sku')
            ->select('sku_sn,sku_id,goods_id')
            ->where_in('goods_id', $goods_ids)
            ->order_by("FIELD(goods_id," . implode(',', $goods_ids) . ")")
            ->get();

        $_list = $query->result_array();

        return $_list;
    }

    public function goods_count($param = array())
    {
        if ($param['cate_id']) {
            $end_cate_id = end($param['cate_id']);
            $cate = $this->cate($end_cate_id);
            $param['cate_id'] += $cate;
        }

        $this->db->from('goods as g');

        $this->_where($param);


        return $this->db->count_all_results();
    }


    private function _where($param)
    {
        if ($param['is_on_sale'] != 2) {
            $param['is_on_sale'] = $param['is_on_sale'] == 1 ? 1 : 0;
            $this->db->where("g.is_on_sale = '{$param['is_on_sale']}'");
        }

        if ($param['cate_id']) {
            $this->db->where_in('g.cat_id', $param['cate_id']);

        }

        if($param['goods_ids']){
            if(!is_array($param['goods_ids'])){
                $param['goods_ids'] = [$param['goods_ids']];
            }

            $this->db->where_in('goods_id', $param['goods_ids']);
        }

        if ($param['admin_id']) {
            $this->db->where("g.admin_id = '{$param['admin_id']}'");
        }

        if ($param['goods_sn']) {

            $this->db->where_in("g.goods_sn", $param['goods_sn']);
        }

        if ($param['suppliers_id']) {
            $this->db->where("g.suppliers_id = '{$param['suppliers_id']}'");
        }

        if ($param['on_sale_time'][0] && $param['on_sale_time'][1]) {

            $on_sale_time_start = strtotime($param['on_sale_time'][0]);
            $on_sale_time_end = strtotime($param['on_sale_time'][1]);

            if ($param['web_id']) {
                $web_ids_str = " WHERE web_id in ( " . implode(',', $param['web_id']) . ")";
            }

            $this->db->join("(
	SELECT a.sg_id,a.goods_id, a.add_time FROM pcs_shopify_goods AS a
	LEFT JOIN (
		SELECT MIN(sg_id) AS sg_id, goods_id FROM pcs_shopify_goods {$web_ids_str} GROUP BY goods_id
	) AS b ON a.goods_id = b.goods_id
WHERE a.sg_id = b.sg_id
) AS sg", "g.goods_id = sg.goods_id", 'left');

            $this->db->where("sg.add_time >= '$on_sale_time_start' and sg.add_time <= '$on_sale_time_end'");

        }



    }


    public function country_order($goods_ids, $web_ids)
    {
        $query = $this->db->from('order_goods as og')
            ->select('oi.country,og.goods_id,SUM(og.goods_number) as goods_number')
            ->join('order_info as oi', 'og.order_id = oi.order_id', 'left')
            ->where_in("og.goods_id", $goods_ids)
            ->where_in('oi.web_id', $web_ids)
            ->group_by('oi.country,og.goods_id')
            ->order_by("FIELD(goods_id," . implode(',', $goods_ids) . "), goods_number DESC")
            ->get();
        $_list = $query->result_array();

        $list = array();
        foreach ($_list as $item) {
            $list[' ' . $item['goods_id']][] = $item;
        }

        return $list;
    }

    public function ga_goods_sku_list($param) {
        $list = array();
        $goods = array();
        $_list = array();

        if (!$param['goods_id']) {
            return false;
        }


        if ($param['goods_id']) {
            $this->db->from('ga_goods as gg');
            $this->db->select('gg.*,gs.color,gs.size');
            $this->db->join('goods_sku as gs','gs.sku_id = gg.sku_id', 'left');



            if ($param['statistical_time'][0] && $param['statistical_time'][1]) {
                $_day_start =  strtotime(date("Y-m-d",strtotime($param['statistical_time'][0])));
                $_day_end = strtotime(date("Y-m-d",strtotime($param['statistical_time'][1])));
                $this->db->where("gg.ga_date >= '{$_day_start}' and gg.ga_date <= '{$_day_end}'");
            } else {
                $_day =  strtotime(date("Y-m-d",strtotime("-7 day")));
                $this->db->where("gg.ga_date >= '{$_day}'");
            }

            $this->db->where('gg.goods_id', $param['goods_id']);
            $this->db->order_by('gg.ga_date DESC');

            $query = $this->db->get();

            $_list = $query->result_array();
        }

        $day = array(
            'all_day' => array('title' => '总累计', 'day' => 0),
            '3_day' => array('title' => '近3天', 'day' => strtotime(date("Y-m-d",strtotime("-3 day")))),
            '7_day' => array('title' => '近7天', 'day' => strtotime(date("Y-m-d",strtotime("-7 day")))),
            '30_day' => array('title' => '近30天', 'day' => strtotime(date("Y-m-d",strtotime("-30 day")))),
            'tom_day' => array('title' => '本月累计', 'day' => strtotime(date("Y-m-01",time())))
        );

        foreach ($_list as $item) {
            $color = str_replace(' ', '-', $item['color']);

            $list[$color]['date'][$item['ga_date']]['detail_views'] += $item['detail_views'];
            $list[$color]['date'][$item['ga_date']]['adds_cart'] += $item['adds_cart'];
            $list[$color]['date'][$item['ga_date']]['item_quantity'] += $item['item_quantity'];
            $list[$color]['date'][$item['ga_date']]['order_quantity'] += $item['order_quantity'];

            $list[$color]['item'][$item['size']][$item['ga_date']]['detail_views'] = $item['detail_views'];
            $list[$color]['item'][$item['size']][$item['ga_date']]['adds_cart'] = $item['adds_cart'];
            $list[$color]['item'][$item['size']][$item['ga_date']]['item_quantity'] = $item['item_quantity'];
            $list[$color]['item'][$item['size']][$item['ga_date']]['order_quantity'] = $item['order_quantity'];
        }

        foreach ($day as $k => $val) {
            $row_ga_goods = $this->day_ga_goods_sku($param, $val['day']);
            foreach ($row_ga_goods as $row) {
                $color = str_replace(' ', '-', $row['color']);
                $list[$color]['date'][$k]['detail_views'] += $row['detail_views'];
                $list[$color]['date'][$k]['adds_cart'] += $row['adds_cart'];
                $list[$color]['date'][$k]['item_quantity'] += $row['item_quantity'];
                $list[$color]['date'][$k]['order_quantity'] += $row['order_quantity'];

                $list[$color]['item'][$row['size']][$k]['detail_views'] = $row['detail_views'];
                $list[$color]['item'][$row['size']][$k]['adds_cart'] = $row['adds_cart'];
                $list[$color]['item'][$row['size']][$k]['item_quantity'] = $row['item_quantity'];
                $list[$color]['item'][$row['size']][$k]['order_quantity'] = $row['order_quantity'];
            }
        }

        return $list;

    }

    public function day_ga_goods_sku($param = array(), $day)
    {
        $this->db->from('ga_goods as gg');
        $this->db->select('SUM(gg.detail_views) as detail_views,SUM(gg.adds_cart) as adds_cart, SUM(gg.item_quantity) as item_quantity, SUM(gg.order_quantity) as order_quantity, gg.sku_id, gs.color,gs.size');
        $this->db->join('goods_sku as gs', 'gg.sku_id = gs.sku_id', 'left');
        $this->db->where('gg.goods_id', $param['goods_id']);
        $this->db->group_by('gg.sku_id');

        if ($day > 0 ) {
            $this->db->where("gg.ga_date >= '{$day}'");
        } else {
            if ($param['statistical_time'][0] && $param['statistical_time'][1]) {
                $_day_start =  strtotime(date("Y-m-d",strtotime($param['statistical_time'][0])));
                $_day_end = strtotime(date("Y-m-d",strtotime($param['statistical_time'][1])));
                $this->db->where("gg.ga_date >= '{$_day_start}' and gg.ga_date <= '{$_day_end}'");
            }
        }

        $_row_query = $this->db->get();

        $_row_ga_goods = $_row_query->result_array();

        return $_row_ga_goods;
    }

    public function ga_goods_list($param = array())
    {

        $list = array();
        $goods = array();
        $_list = array();

        if (!$param['goods_ids']) {
            return false;
        }


        if ($param['goods_ids']) {
            $this->db->from('ga_goods');
            $this->db->select('SUM(detail_views) as detail_views,SUM(adds_cart) as adds_cart, SUM(item_quantity) as item_quantity, SUM(order_quantity) as order_quantity, ga_date, goods_id');



            if ($param['statistical_time'][0] && $param['statistical_time'][1]) {
                $_day_start =  strtotime(date("Y-m-d",strtotime($param['statistical_time'][0])));
                $_day_end = strtotime(date("Y-m-d",strtotime($param['statistical_time'][1])));
                $this->db->where("ga_date >= '{$_day_start}' and ga_date <= '{$_day_end}'");
            } else {
                $_day =  strtotime(date("Y-m-d",strtotime("-7 day")));
                $this->db->where("ga_date >= '{$_day}'");
            }

            if ($param['web_id']) {
                $this->db->where_in('web_id', $param['web_id']);
            }

            $this->db->where_in('goods_id', $param['goods_ids']);
            $this->db->group_by('goods_id,ga_date');
            $this->db->order_by('ga_date DESC');

            $query = $this->db->get();

            $_list = $query->result_array();
        }

        $day = array(
            'all_day' => array('title' => '总累计', 'day' => 0),
            '3_day' => array('title' => '近3天', 'day' => strtotime(date("Y-m-d",strtotime("-3 day")))),
            '7_day' => array('title' => '近7天', 'day' => strtotime(date("Y-m-d",strtotime("-7 day")))),
            '30_day' => array('title' => '近30天', 'day' => strtotime(date("Y-m-d",strtotime("-30 day")))),
            'tom_day' => array('title' => '本月累计', 'day' => strtotime(date("Y-m-01",time())))
        );

        foreach ($day as $k => $val) {
            $list['title_list'][$k] = $val['title'];

            $row_ga_goods = $this->day_ga_goods($param, $val['day']);

            foreach ($param['goods_ids'] as $_goods_id) {

                $goods[$_goods_id.$k] = array(
                    'detail_views' => intval($row_ga_goods[$_goods_id]['detail_views']),
                    'adds_cart' => intval($row_ga_goods[$_goods_id]['adds_cart']),
                    'item_quantity' => intval($row_ga_goods[$_goods_id]['item_quantity']),
                    'order_quantity' => intval($row_ga_goods[$_goods_id]['order_quantity'])
                );
            }
        }

        $day['tom_day'] = strtotime(date("Y-m-01",time()));
        $day['7_day'] = strtotime(date("Y-m-d",strtotime("-7 day")));
        /*$day['3_day'] = strtotime(date("Y-m-d",strtotime("-3 day")));*/


        foreach ($_list as $item) {

            $goods[$item['goods_id'].$item['ga_date']] = $item;


            $list['title_list'][$item['ga_date']] = date('m月d日', $item['ga_date']);
        }

        foreach ($param['goods_ids'] as $goods_id) {
            foreach ($list['title_list'] as $k => $title) {
                $list['data'][$goods_id][$k] = array(
                    'detail_views' => intval($goods[$goods_id.$k]['detail_views']),
                    'adds_cart' => intval($goods[$goods_id.$k]['adds_cart']),
                    'item_quantity' => intval($goods[$goods_id.$k]['item_quantity']),
                    'order_quantity' => intval($goods[$goods_id.$k]['order_quantity'])
                );
            }

        }

        return $list;
    }


    public function day_ga_goods($param = array(), $day)
    {
        $this->db->from('ga_goods');
        $this->db->select('SUM(detail_views) as detail_views,SUM(adds_cart) as adds_cart, SUM(item_quantity) as item_quantity, SUM(order_quantity) as order_quantity, goods_id');
        $this->db->where_in('goods_id', $param['goods_ids']);
        $this->db->group_by('goods_id');

        if ($param['web_id']) {
            $this->db->where_in('web_id', $param['web_id']);
        }

        if ($day > 0 ) {
            $this->db->where("ga_date >= '{$day}'");
        } else {
            if ($param['statistical_time'][0] && $param['statistical_time'][1]) {
                $_day_start =  strtotime(date("Y-m-d",strtotime($param['statistical_time'][0])));
                $_day_end = strtotime(date("Y-m-d",strtotime($param['statistical_time'][1])));
                $this->db->where("ga_date >= '{$_day_start}' and ga_date <= '{$_day_end}'");
            }
        }

        $_row_query = $this->db->get();

        $_row_ga_goods = $_row_query->result_array();

        $row_ga_goods = array();
        foreach ($_row_ga_goods as $row) {
            $row_ga_goods[$row['goods_id']] = $row;
        }

        return $row_ga_goods;
    }


    public function web_ga_goods($goods_id, $param = array())
    {

        $site_web = $this->siteweb_option();

        $this->db->from('ga_goods');
        $this->db->where('goods_id', $goods_id);

        $list = array();
        if ($param['start_time'] && $param['end_time']) {

            $start_time = strtotime(date('Y-m-d', strtotime($param['start_time'])));
            $end_time = strtotime(date('Y-m-d', strtotime($param['end_time'])));

            $_start_time = $start_time - 24 * 3600;
            while ($_start_time <= $end_time) {
                $list['labels'][] = date('m-d', $_start_time);
                $_start_time += 24 * 3600;
            }

            $this->db->where("ga_date >= '{$start_time}' and ga_date <= '{$end_time}'");
        } else {
            $_7_day = strtotime(date("Y-m-d",strtotime("-7 day")));
            $today = strtotime('today');
            $j_7_day = $_7_day;
            while ($j_7_day < $today) {
                $list['labels'][] = date('m-d', $j_7_day);
                $j_7_day += 24 * 3600;
            }

            $this->db->where("ga_date >= '{$_7_day}'");
        }

        $this->db->order_by('ga_date ASC');

        $query = $this->db->get();

        $_list = $query->result_array();

        $datasets = array();
        foreach ($_list as $row) {
            $md = date('m-d', $row['ga_date']);
            $datasets['detail_views'][$md] += $row['detail_views'];
            $datasets['adds_cart'][$md] += $row['adds_cart'];
            $datasets['item_quantity_count'][$md] += $row['item_quantity'];
            $datasets['item_quantity'][$row['web_id']][$md] += $row['item_quantity'];
        }

        $list['datasets'][] = array(
            'label' => '浏览量',
            'borderColor' => '#249ec7',
            'backgroundColor' => '#249ec7',
            'borderDash' => array(5,5),
            'hidden' => true,
            'fill' => false,
            'data' => array_values($datasets['detail_views']),
            'yAxisID' => "y-web-1",
            'ticks' => array(
                'min' => 1
            )
        );
        $list['yAxes'][] = array(
            'type' => 'linear', // only linear but allow scale type registration. This allows extensions to exist solely for log scale for instance
            'display' => true,
            'position' => 'left',
            'id' => "y-web-1"
        );

        $adds_cart = array();
        foreach ($datasets['adds_cart'] as $k => $_cart) {
            $adds_cart[] = sprintf('%.4f', $_cart / $datasets['detail_views'][$k]) * 100;
        }

        $list['datasets'][] = array(
            'label' => '加车率',
            'borderColor' => '#119c4c',
            'backgroundColor' => '#119c4c',
            'borderDash' => array(5,5),
            'hidden' => true,
            'fill' => false,
            'data' => $adds_cart,
            'yAxisID' => "y-cart-1"
        );
        $list['yAxes'][] = array(
            'type' => 'linear', // only linear but allow scale type registration. This allows extensions to exist solely for log scale for instance
            'display' => true,
            'position' => 'right',
            'id' => "y-cart-1",
            'ticks' => array(
                'suggestedMin' => 0,
                'suggestedMax' => 100
            )
        );

        $item_quantity_count = array();
        foreach ($datasets['item_quantity_count'] as $_k => $_quantity_count) {
            $item_quantity_count[] = sprintf('%.4f', $_quantity_count / $datasets['detail_views'][$_k]) * 100;
        }


        $list['datasets'][] = array(
            'label' => '转换率',
            'borderColor' => '#d14a61',
            'backgroundColor' => '#d14a61',
            'hidden' => true,
            'borderDash' => array(5,5),
            'fill' => false,
            'data' => $item_quantity_count,
            'yAxisID' => "y-cart-1"
        );
       /* $list['yAxes'][] = array(
            'type' => 'linear', // only linear but allow scale type registration. This allows extensions to exist solely for log scale for instance
            'display' => true,
            'position' => 'right',
            'id' => "y-quantity-1",
            'ticks' => array(
                'suggestedMin' => 0,
                'suggestedMax' => 100
            )
        );*/


        foreach ($datasets['item_quantity'] as $k => $item_quantity) {
            $color = $this->random_color();
            $list['datasets'][$k] = array(
                'label' => $site_web[$k],
                'borderColor' => $color,
                'backgroundColor' => $color,
                'fill' => false,
                'data' => array_values($item_quantity),
                'yAxisID' => "y-web-1"
            );
        }
        $list['yAxes'][] = array(
            'type' => 'linear', // only linear but allow scale type registration. This allows extensions to exist solely for log scale for instance
            'display' => true,
            'position' => 'left',
            'id' => "y-web-1"
        );
        $list['datasets'] = array_values($list['datasets']);

        return $list;
    }

    public function siteweb_option($is_all = 0)
    {
        $this->db->from('siteweb');

        if($is_all) {
            $this->db->where('is_delete', 0);
        }


        $query = $this->db->get();

        $siteweb = array();
        foreach ($query->result_array() as $row) {
            $siteweb[$row['web_id']] = $row['web_name'];
        }

        return $siteweb;
    }

    function random_color(){
        mt_srand((double)microtime()*1000000);
        $c = '';
        while(strlen($c)<6){
            $c .= sprintf("%02X", mt_rand(0, 255));
        }
        return '#' . $c;
    }

    public function insert($data = array())
    {
        $_sku = $data['sku'];
        $_web = $data['web'];

        if (!$_sku) {
            $this->db->update('siteweb', array(
                'update_time' => strtotime('today'),
                'update_state' => 2
            ), "web_id = '{$_web['web_id']}'");

            return false;
        }

        //$sku_sn = "'" . str_replace($_web['ab_name'], '', implode("','", array_column($_sku, 'ga:productSku'))) . "'";

        $_ga_data = array();
        foreach ($_sku as $item) {
            $goods_list = $this->order_count(str_replace($_web['ab_name'], '', $item['ga:productSku']), $data['date'], $_web);
            if ($item['ga:productSku'] == '(not set)' || !$goods_list) {
                continue;
            }

            $ga_date = strtotime($data['date']);

            $query = $this->db->from('ga_goods')->where("ga_goods_sn = '{$item['ga:productSku']}' and ga_date = '{$ga_date}'")->get();
            $ga_goods = $query->row_array();

            if ($ga_goods) {
                $_ga_data['update'][] = array(
                    'ga_id' => $ga_goods['ga_id'],
                    'ga_goods_sn' => $item['ga:productSku'],
                    'sku_id' => $goods_list['sku_id'],
                    'goods_id' => $goods_list['goods_id'],
                    'web_id' => $_web['web_id'],
                    'adds_cart' => $item['productAddsToCart'],
                    'detail_views' => $item['productDetailViews'],
                    'item_quantity' => $item['itemQuantity'],
                    'order_quantity' => intval($goods_list['goods_number']),
                    'ga_date' => $ga_date
                );
            } else {
                $_ga_data['insert'][] = array(
                    'ga_goods_sn' => $item['ga:productSku'],
                    'sku_id' => $goods_list['sku_id'],
                    'goods_id' => $goods_list['goods_id'],
                    'web_id' => $_web['web_id'],
                    'adds_cart' => $item['productAddsToCart'],
                    'detail_views' => $item['productDetailViews'],
                    'item_quantity' => $item['itemQuantity'],
                    'order_quantity' => intval($goods_list['goods_number']),
                    'ga_date' => $ga_date,
                    'add_time' => time()
                );
            }
        }

        if ($_ga_data) {
            if ($_ga_data['update']) {
                $this->db->update_batch('ga_goods', $_ga_data['update'], 'ga_id');
            } else {
                $this->db->insert_batch('ga_goods', $_ga_data['insert']);
            }

            $this->db->update('siteweb', array(
                'update_time' => strtotime('today'),
                'update_state' => 1
            ), "web_id = '{$_web['web_id']}'");
        }


        return true;

    }

    public function order_count($sku_sn, $date, $web)
    {

        $start_time = strtotime(date('Y-m-d 12:00:00', strtotime($date)));
        $end_time = $start_time + 3600 * 24;

        $sql = "select sum(og.goods_number) from pcs_order_goods as og left join pcs_order_info as oi on og.order_id = oi.order_id where og.sku_id = gs.sku_id and oi.pay_time >= '{$start_time}' and oi.pay_time <= '{$end_time}' and oi.web_id = '{$web['web_id']}'";

        $this->db->from('goods_sku as gs')->select("($sql) as goods_number,gs.sku_num_sn,gs.sku_id,gs.goods_id")->where("gs.sku_num_sn = '{$sku_sn}' and gs.is_delete = 0");
        $query = $this->db->get();

        $goods_list = $query->row_array();

//        $_list = array();
//        foreach ($goods_list as $item) {
//            $_list[$web['ab_name'] . $item['sku_num_sn']] = $item;
//        }

        return $goods_list;
    }

    /** 聚合过滤加车率 销售量 转化率
     * @param $params
     * @return bool
     */
    public function goods_sum_filter($params){
        $where = ' where 1 ';

        if(empty($params['web_id'])){
            return false;
        }

        $where .= ' and web_id in ('.implode(',', $params['web_id']).')';

//        if($params['goods_sn']){
//            $CI=&get_instance();
//            $CI->load->model('goods_sku_model');
//            $good_info = $CI->goods_sku_model->j_row(['sku_num_sn' => $params['goods_sn']], 'goods_id');
//
//            $rs_goods_id = $good_info ? $good_info['goods_id'] : -1;
//            $where .= ' and goods_id = '.$rs_goods_id;
//        }

        $having = '';

        if ($params['ll_info'][0] && $params['ll_info'][1]) {
            $having .= " and total_detail_views >= {$params['ll_info'][0]} and total_detail_views <= {$params['ll_info'][1]} ";
        }

        if ($params['jc_info'][0] && $params['jc_info'][1]) {
            $having .= " and total_adds_cart/total_detail_views >= {$params['jc_info'][0]}/100 and total_adds_cart/total_detail_views <= {$params['jc_info'][1]}/100 ";
        }

        if ($params['zh_info'][0] && $params['zh_info'][1]) {
            $having .= " and total_item_quantity/total_detail_views >= {$params['zh_info'][0]}/100 and total_item_quantity/total_detail_views <= {$params['zh_info'][1]}/100 ";
        }

        if(empty($having)){
            return false;
        }

        $having = ' 1 '.$having;

        $sql = <<<EOF
        SELECT
            goods_id,
            web_id,
            sum(detail_views) AS total_detail_views,
            sum(adds_cart) AS total_adds_cart,
            sum(item_quantity) AS total_item_quantity,
            sum(order_quantity) AS total_order_quantity

        FROM
            `pcs_ga_goods`
          {$where}       
        GROUP BY
            goods_id 
		    HAVING {$having}
EOF;
;

        $data = $this->db->query($sql)->result_array();

        $goods_id = [];
        if(empty($data)){
            return $goods_id = [-1];
        }

        foreach($data as $value){
            $goods_id[] = $value['goods_id'];
        }

        return $goods_id;
    }
    
}
